package com.qqqq.dao.impl;

import com.qqqq.dao.IUserDao;
import com.qqqq.domain.User;
import com.qqqq.utils.JdbcUtils;
import com.qqqq.utils.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements IUserDao {

    @Override
    public User getUserByUserName(String username,String password) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from user where username=? and password=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, password);
        ResultSet rs = ps.executeQuery();
        List<User> list = getUserList(rs);
        System.out.println(list);
        rs.close();
        ps.close();
        conn.close();
        return !list.isEmpty() ? list.get(0) : null;

    }


//    @Override
//    public List<User> list(User user) throws SQLException {
//        Connection conn = JdbcUtils.getConnection();
//        String sql = "select * from user where username=? ";
//        if (StringUtils.isNotBlank(user.getUsername())) {
//            sql+="and username=?";
//        }
//        if (StringUtils.isNotBlank(user.getPassword())) {
//            sql+="and password=?";
//        }
//        PreparedStatement ps = conn.prepareStatement(sql);
//        int i=1;
//        if (StringUtils.isNotBlank(user.getUsername())) {
//            ps.setString(i++,"%"+user.getUsername()+"%");
//        }
//        if (StringUtils.isNotBlank(user.getPassword())) {
//            ps.setString(i++,"%"+user.getPassword()+"%");
//        }
//        ResultSet rs = ps.executeQuery();
//        List<User> list = getUserLis(rs);
//        rs.close();
//        ps.close();
//        conn.close();
//        return list;
//    }

    @Override
    public List<User> page(User user, int pageNum, int pageSize) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from user where username=? ";
        if (StringUtils.isNotBlank(user.getUsername())) {
            sql+="and username=?";
        }
        if (StringUtils.isNotBlank(user.getPassword())) {
            sql+="and password=?";
        }
        sql+=" limit ?,?";
        PreparedStatement ps = conn.prepareStatement(sql);
        int i=1;
        if (StringUtils.isNotBlank(user.getUsername())) {
            ps.setString(i++,"%"+user.getUsername()+"%");
        }
        if (StringUtils.isNotBlank(user.getPassword())) {
            ps.setString(i++,user.getPassword());
        }
        ps.setInt(i++,(pageNum-1)*pageSize);
        ps.setInt(i++,pageSize);
        ResultSet rs = ps.executeQuery();
        List<User> list = getUserList(rs);
        rs.close();
        ps.close();
        conn.close();
        return list;
    }

    @Override
    public int count(User user) throws SQLException {
       Connection conn = JdbcUtils.getConnection();
       String sql = "select count(*) from user where 1=1";
       if (StringUtils.isNotBlank(user.getUsername())) {
           sql+="and username=?";
       }
       if (StringUtils.isNotBlank(user.getPassword())) {
           sql+="and password=?";
       }
       PreparedStatement ps = conn.prepareStatement(sql);
       int i=1;
       if (StringUtils.isNotBlank(user.getUsername())) {
           ps.setString(i++,"%"+user.getUsername()+"%");
       }
       if (StringUtils.isNotBlank(user.getPassword())) {
           ps.setString(i++,user.getPassword());
       }
       ResultSet rs = ps.executeQuery();
       int count=0;
       if (rs.next()) {
           count = rs.getInt("count");
       }
//       rs.close();
       ps.close();
       conn.close();
       return count;
    }

    @Override
    public User getUser(int id) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from user where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,id);
        ResultSet rs = ps.executeQuery();
        List<User> list = getUserList(rs);
//        rs.close();
        ps.close();
        conn.close();
        if (!list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }


    @Override
    public int addUser(User user) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "insert into user(username,password,state) values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,user.getUsername());
        ps.setString(2,user.getPassword());
        ps.setString(3,user.getState());
        int i=ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }

    @Override
    public int updateUser(User user) throws SQLException {
        Connection conn=JdbcUtils.getConnection();
        String sql = "update user set username=?,password=?,state=? where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,user.getUsername());
        ps.setString(2,user.getPassword());
        ps.setString(3,user.getState());
        ps.setInt(4,user.getId());
        int i=ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }

    @Override
    public int deleteStudent(int id) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "delete from user where id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,id);
        int i=ps.executeUpdate();
        ps.close();
        conn.close();
        return i;
    }

    @Override
    public List<User> getUserList(ResultSet rs) throws SQLException {
        List<User> list = new ArrayList<>();
        while (rs.next()) {
            list.add(new User(
                    rs.getInt("id"),
                    rs.getString("username"),
                    rs.getString("password"),
                    rs.getString("state")));
        }
        return list;
    }




}